package com.ita.acmsystem.merchant.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ita.acmsystem.merchant.dao.IMerchantDao;
import com.ita.acmsystem.merchant.entity.Merchant;
import com.ita.acmsystem.merchant.util.DBUtil;

public class MerchantDaoImpl implements IMerchantDao {

	@Override
	public Merchant query(String merchantName, String merchantPassword) {

		Merchant merchant = null;

		String sql = "select * from merchant where merchant_name=? and merchant_password=?";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchantName);
			ps.setString(2, merchantPassword);
			rs = ps.executeQuery();

			if (rs.next()) {
				merchant = new Merchant();
				merchant.setMerchantId(rs.getString(1));
				merchant.setMerchantName(rs.getString(2));
				merchant.setMerchantPassword(rs.getString(3));
				merchant.setMerchantAddress(rs.getString(4));
				merchant.setMerchantPic(rs.getString(5));
				merchant.setMerchantIdcard(rs.getString(6));
				merchant.setMerchantBlanklist(rs.getInt(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return merchant;
	}

	@Override
	public Merchant queryById(String merchantId) {

		Merchant merchant = null;

		String sql = "select * from merchant where merchant_id=?";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchantId);
			rs = ps.executeQuery();

			if (rs.next()) {
				merchant = new Merchant();
				merchant.setMerchantId(rs.getString(1));
				merchant.setMerchantName(rs.getString(2));
				merchant.setMerchantPassword(rs.getString(3));
				merchant.setMerchantAddress(rs.getString(4));
				merchant.setMerchantPic(rs.getString(5));
				merchant.setMerchantIdcard(rs.getString(6));
				merchant.setMerchantBlanklist(rs.getInt(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return merchant;
	}

	@Override
	public List<Merchant> queryAll() {
		List<Merchant> merchants = new ArrayList<Merchant>();
		Merchant merchant = null;

		String sql = "select * from merchant";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				merchant = new Merchant();
				merchant.setMerchantId(rs.getString(1));
				merchant.setMerchantName(rs.getString(2));
				merchant.setMerchantPassword(rs.getString(3));
				merchant.setMerchantAddress(rs.getString(4));
				merchant.setMerchantPic(rs.getString(5));
				merchant.setMerchantIdcard(rs.getString(6));
				merchant.setMerchantBlanklist(rs.getInt(7));
				merchants.add(merchant);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return merchants;
	}

	@Override
	public Merchant persist(Merchant merchant) {

		Merchant result = null;

		String sql = "insert into merchant values(?,?,?,?,?,?,?)";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchant.getMerchantId());
			ps.setString(2, merchant.getMerchantName());
			ps.setString(3, merchant.getMerchantPassword());
			ps.setString(4, merchant.getMerchantAddress());
			ps.setString(5, merchant.getMerchantPic());
			ps.setString(6, merchant.getMerchantIdcard());
			ps.setInt(7, merchant.getMerchantBlanklist());
			rs = ps.executeQuery();

			result = merchant;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return result;
	}

	@Override
	public boolean update(Merchant merchant) {

		boolean result = false;

		String sql = "update merchant set merchant_name=?,"
				+ "merchant_password=?,merchant_address=?,"
				+ "merchant_pic=?,merchant_idcard=?,"
				+ "merchant_blacklist=? where merchant_id=?";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchant.getMerchantName());
			ps.setString(2, merchant.getMerchantPassword());
			ps.setString(3, merchant.getMerchantAddress());
			ps.setString(4, merchant.getMerchantPic());
			ps.setString(5, merchant.getMerchantIdcard());
			ps.setInt(6, merchant.getMerchantBlanklist());
			ps.setString(7, merchant.getMerchantId());
			rs = ps.executeQuery();

			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return result;
	}

	@Override
	public boolean delete(String merchantId) {
		boolean result = false;

		String sql = "delete from merchant where merchant_id=?";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchantId);
			rs = ps.executeQuery();

			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return result;
	}

	@Override
	public Merchant queryByName(String merchantName) {
		Merchant merchant = null;

		String sql = "select * from merchant where merchant_name=?";

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, merchantName);
			rs = ps.executeQuery();

			if (rs.next()) {
				merchant = new Merchant();
				merchant.setMerchantId(rs.getString(1));
				merchant.setMerchantName(rs.getString(2));
				merchant.setMerchantPassword(rs.getString(3));
				merchant.setMerchantAddress(rs.getString(4));
				merchant.setMerchantPic(rs.getString(5));
				merchant.setMerchantIdcard(rs.getString(6));
				merchant.setMerchantBlanklist(rs.getInt(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return merchant;
	}

}
